# -*- coding = utf-8 -*-
# @Time : 2023/5/29 10:16
# @Author : GHHHHHHHHH
# @File : viewDao.py
# @Software : PyCharm
from sqlalchemy import text

from dao import db


class AlgorithmDao:
    @staticmethod
    def select_username(username):
        sql = text('SELECT id, username FROM test_table WHERE username = "%s"' % username)
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['id', 'username'], data1)) for data1 in data]

    @staticmethod
    def save_plate(car_number, is_registeration, driver_id):
        if driver_id is None:
            sql = text(
                'INSERT INTO registration_car (car_number, is_registration, create_time, update_time)'
                ' VALUES ("%s", %d, now(), now())' % (car_number, is_registeration))
        else:
            sql = text('INSERT INTO registration_car (car_number, is_registration, driver_id, create_time, update_time)'
                       ' VALUES ("%s", %d, %d, now(), now())' % (car_number, is_registeration, driver_id))
        try:
            db.session.execute(sql)
            db.session.commit()
        except Exception as e:
            print(e)
            db.session.rollback()
            return False
        return True

    @staticmethod
    def select_driver_id_by_car_number(car_number):
        sql = text('SELECT dispatch_car.driver_id FROM dispatch_car, message_car '
                   'WHERE message_car.car_number = "%s" AND message_car.id = dispatch_car.car_id' % car_number)
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['driver_id'], data1)) for data1 in data]

    @staticmethod
    def ensure_is_registeration(car_number):
        sql = text('SELECT id FROM message_car WHERE car_number = "%s"' % car_number)
        data = db.session.execute(sql).fetchall()
        if len([dict(zip(['driver_id'], data1)) for data1 in data]) == 0:
            return False
        return True

    @staticmethod
    def bayonet_msg(user_id):
        sql = text("SELECT place, longitude, latitude FROM message_bayonet WHERE user_id = %d" % user_id)
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['place', 'longitude', 'latitude'], data1)) for data1 in data]

    @staticmethod
    def bayonet_passed_company(bayonet_id):
        sql = text("SELECT car_number, driver_id, create_time FROM registration_car where is_registration = 1")
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['car_number', 'driver_id', 'passed_time'], data1)) for data1 in data]

    @staticmethod
    def bayonet_passed_private(bayonet_id):
        sql = text("SELECT car_number, create_time FROM registration_car where is_registration = 0")
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['car_number', 'passed_time'], data1)) for data1 in data]

    @staticmethod
    def bayonet_get_driver(car_number, passed_time):
        sql = text('SELECT job_user.job_number, job_user.employee_name, job_user.entry_time, job_user.age, '
                   'job_user.photo_url, drive_user.driving_experience, type_car.motorcycle_type '
                   'FROM job_user, drive_user, type_car, registration_car WHERE registration_car.car_number = "%s" '
                   'and job_user.id = drive_user.user_id and drive_user.id = registration_car.driver_id '
                   'and type_car.id = drive_user.type_car_id and registration_car.create_time = "%s"'
                   % (car_number, passed_time))
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['job_number', 'employee_name', 'entry_time', 'age', 'photo_url',
                          'driving_experience', 'motorcycle_type'], data1)) for data1 in data]

    @staticmethod
    def infoDep_pieChart():
        sql = text('SELECT motorcycle_type FROM type_car')
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['motorcycle_type'], data1)) for data1 in data]

    @staticmethod
    def get_carNum():
        sql = text(
            "SELECT id FROM dispatch_car where create_time between DATE(subdate(curdate()-1 ,date_format(curdate()-1,'%w')-1)) and  DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-7))")
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['id'], data1)) for data1 in data]

    @staticmethod
    def get_mile():
        sql = text(
            "SELECT mileage FROM dispatch_car where create_time between DATE(subdate(curdate()-1 ,date_format(curdate()-1,'%w')-1)) and  DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-7))")
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['mile'], data1)) for data1 in data]

    @staticmethod
    def get_flow():
        sql = text(
            "SELECT id FROM registration_car where create_time between DATE(subdate(curdate()-1 ,date_format(curdate()-1,'%w')-1)) and  DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-7))")
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['id'], data1)) for data1 in data]

    @staticmethod
    def get_shang_gong_num():
        sql = text(
            "SELECT driver_id FROM dispatch_car "
            "where create_time "
            "between DATE(subdate(curdate()-1 ,date_format(curdate()-1,'%w')-1))"
            " and  DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-7))")
        data = db.session.execute(sql).fetchall()
        return data

    @staticmethod
    def get_driver_num():
        sql = text('select id from drive_user')
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['id'], data1)) for data1 in data]

    @staticmethod
    def get_car(is_registration):
        sql = text('select id, driver_id, car_number, create_time from registration_car'
                   ' where is_registration = %d' % is_registration)
        data = db.session.execute(sql).fetchall()
        return [dict(zip(['id', 'driver_id', 'car_number', 'create_time'], data1)) for data1 in data]

    @staticmethod
    def ftp_server(_id, filename):
        sql = text(
            'UPDATE job_user SET photo_url'
            ' = "%s" WHERE id = %d' % (f'101.200.182.134:8080/picture/{filename}', _id))
        try:
            db.session.execute(sql)
            db.session.commit()
        except Exception as e:
            print(e)
            db.session.rollback()
            return False
        return True